package merge;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.List;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class CopyExcelSheetToAnotherExcelSheet {

	
	public static void merge(String fromPath,String fileName,List<String> files) throws FileNotFoundException, IOException {
		// 新的excel 文件名
		String excelName = "节目访问量";
		// 创建新的excel
		XSSFWorkbook wbCreat = new XSSFWorkbook();

		for (String excel : files) {
			// 打开已有的excel
			String strExcelPath = fromPath + "\\" + excel;
			InputStream in = new FileInputStream(strExcelPath);
			XSSFWorkbook wb = new XSSFWorkbook(in);
			for (int ii = 0; ii < wb.getNumberOfSheets(); ii++) {
				XSSFSheet sheet = wb.getSheetAt(ii);//getSheetAt(ii);
				int firstRow = sheet.getFirstRowNum();
				int lastRow = sheet.getLastRowNum();
				if(lastRow == 0){
					continue;
				}
				XSSFSheet sheetCreat = wbCreat.createSheet(sheet.getSheetName());
				// 复制源表中的合并单元格
				MergerRegion(sheetCreat, sheet);
			
				
				for (int i = firstRow; i <= lastRow; i++) {
					// 创建新建excel Sheet的行
					XSSFRow rowCreat = sheetCreat.createRow(i);
					// 取得源有excel Sheet的行
					XSSFRow row = sheet.getRow(i);
					// 单元格式样
					if(row!=null){
						
						int firstCell = row.getFirstCellNum();
						int lastCell = row.getLastCellNum();
						for (int j = firstCell; j < lastCell; j++) {
							// 自动适应列宽 貌似不起作用
							// sheetCreat.autoSizeColumn(j);
							System.out.println(row.getCell(j));
							rowCreat.createCell(j);
							String strVal = "";
							if (row.getCell(j) == null) {
								
							} else {
								try {
									strVal = removeInternalBlank(row.getCell(j).getStringCellValue());
									
								} catch (Exception e) {
									// TODO: handle exception
								}
							}
							rowCreat.getCell(j).setCellValue(strVal);
						}
					}
				}
			}
		}
		FileOutputStream fileOut = new FileOutputStream(fromPath +"\\" +excelName + ".xlsx");
		wbCreat.write(fileOut);
		fileOut.close();
	}

	/**
	 * 复制原有sheet的合并单元格到新创建的sheet
	 * 
	 * @param sheetCreat
	 *            新创建sheet @param sheet 原有的sheet
	 */

	private static void MergerRegion(XSSFSheet sheetCreat,XSSFSheet sheet) {
		int sheetMergerCount = sheet.getNumMergedRegions();
		for (int i = 0; i < sheetMergerCount; i++) {
			CellRangeAddress mergedRegionAt = sheet.getMergedRegion(i);//.getMergedRegionAt(i);
			sheetCreat.addMergedRegion(mergedRegionAt);
		}

	}

	/**
	 * 去除字符串内部空格
	 */

	public static String removeInternalBlank(String s) {
		// System.out.println("bb:" + s);
		Pattern p = Pattern.compile("\\s*|\t|\r|\n");
		Matcher m = p.matcher(s);
		char str[] = s.toCharArray();
		StringBuffer sb = new StringBuffer();
		for (int i = 0; i < str.length; i++) {
			if (str[i] == ' ') {
				sb.append(' ');
			} else {
				break;
			}
		}
		String after = m.replaceAll("");
		return sb.toString() + after;
	}
}